package cn.edu.sdjzu.xg.kcsj.security;

import cn.edu.sdjzu.xg.kcsj.domain.Faculty;
import cn.edu.sdjzu.xg.kcsj.exception.BysjException;
import cn.edu.sdjzu.xg.kcsj.service.FacultyService;
import cn.edu.sdjzu.xg.kcsj.util.CommonDao;
import cn.edu.sdjzu.xg.kcsj.util.Condition;
import cn.edu.sdjzu.xg.kcsj.util.JdbcHelper;
import cn.edu.sdjzu.xg.kcsj.util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.List;
import java.util.TreeSet;

public class UserDao {
    //构造器私有化
    private UserDao() {}
    private static UserDao userDao = null;
    //获得实例的方法
    public static UserDao getInstance() {
        if(userDao == null){
            userDao = new UserDao();
        }
        return userDao;
    }

    //查找全部User
    public Collection<User> findAll(List conditionList, Pagination pagination, Connection connection) throws SQLException {
        int totalNum = CommonDao.getInstance().count(connection,"user",conditionList);
        //创建查询的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM user ");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        if(pagination!=null){
            String LimitClause = pagination.toLimitClause(totalNum);
            sql_select.append(LimitClause);
        }
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());
        //创建结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        //创建集合盛放user对象
        Collection<User> users = new TreeSet<User>();
        while (resultSet.next()) {
            //用结果集中的字段为user对象的各字段赋值
            int id = resultSet.getInt("id");
            String username = resultSet.getString("username");
            String password = resultSet.getString("password");
            String name = resultSet.getString("name");
            String sex = resultSet.getString("sex");
            String politicsStatus = resultSet.getString("politicsStatus");
            String telephone = resultSet.getString("telephone");
            Faculty faculty = FacultyService.getInstance().find(
                    resultSet.getInt("faculty_id"));
            Boolean available = resultSet.getBoolean("available");
            String introduction = resultSet.getString("introduction");
            Collection<Role> roles = UserAssRoleService.getInstance().findRolesByUserId(id);
            users.add(new User(id,username,password,name,sex,politicsStatus,
                    telephone,faculty,available,introduction,roles));
        }
        JdbcHelper.close(resultSet,pstmt);
        return users;
    }

    //查找一个user
    public User find(Integer id,Connection connection) throws SQLException {
        User user = null;
        String select_sql = "SELECT * FROM User WHERE id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(select_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //创建结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        while (resultSet.next()) {
            //用结果集中的字段为user对象的各字段赋值
            String username = resultSet.getString("username");
            String password = resultSet.getString("password");
            String name = resultSet.getString("name");
            String sex = resultSet.getString("sex");
            String politicsStatus = resultSet.getString("politicsStatus");
            String telephone = resultSet.getString("telephone");
            Faculty faculty = FacultyService.getInstance().find(
                    resultSet.getInt("faculty_id"));
            Boolean available = resultSet.getBoolean("available");
            String introduction = resultSet.getString("introduction");
            Collection<Role> roles = UserAssRoleService.getInstance().findRolesByUserId(id);
            user = new User(id,username,password,name,sex,politicsStatus,
                    telephone,faculty,available,introduction,roles);
        }
        JdbcHelper.close(resultSet,pstmt);
        return user;
    }

    // 用户自己更新部分信息
    public boolean updateBySelf(User user, Connection connection) throws SQLException {
        String update_sql = "UPDATE User SET telephone=?,introduction=? WHERE id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(update_sql);
        //为预编译参数赋值
        pstmt.setString(1, user.getTelephone());
        pstmt.setString(2, user.getIntroduction());
        pstmt.setInt(3,user.getId());
        //判断是否修改成功
        int effectedRow = pstmt.executeUpdate();
        JdbcHelper.close(pstmt);
        return effectedRow > 0;
    }


    // 管理员更新user信息
    public boolean updateByAdmin(User user,Connection connection) throws SQLException {
        String update_sql = "UPDATE User SET username =?,password =?,name=?,sex=?," +
                "politicsStatus=?,faculty_id=?,available=? WHERE id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(update_sql);
        //为预编译参数赋值
        pstmt.setString(1, user.getUsername());
        pstmt.setString(2, user.getPassword());
        pstmt.setString(3,user.getName());
        pstmt.setString(4,user.getSex());
        pstmt.setString(5,user.getPoliticsStatus());
        pstmt.setInt(6,user.getFaculty().getId());
        pstmt.setBoolean(7,user.getAvailable());
        pstmt.setInt(8, user.getId());
        //判断是否修改成功
        int effectedRow = pstmt.executeUpdate();
        JdbcHelper.close(pstmt);
        return effectedRow > 0;
    }

    // 删除user
    public boolean delete(Integer id,Connection connection) throws SQLException {
        String delete_sql = "DELETE FROM User WHERE id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(delete_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //判断是否删除成功
        int effectedRow = pstmt.executeUpdate();
        JdbcHelper.close(pstmt);
        return effectedRow > 0;
    }

    // 增加user
    public Boolean add(User user,Connection connection) throws SQLException {
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(
                "INSERT INTO User (username,password,name,sex," +
                        "politicsStatus,telephone,faculty_id,available," +
                        "introduction)" +
                        "VALUES(?,?,?,?,?,?,?,?,?)");
        //为预编译参数赋值
        pstmt.setString(1, user.getUsername());
        pstmt.setString(2, user.getPassword());
        pstmt.setString(3,user.getName());
        pstmt.setString(4,user.getSex());
        pstmt.setString(5,user.getPoliticsStatus());
        pstmt.setString(6,user.getTelephone());
        pstmt.setInt(7,user.getFaculty().getId());
        pstmt.setBoolean(8,user.getAvailable());
        pstmt.setString(9,user.getIntroduction());
        int effectedRow = pstmt.executeUpdate();
        JdbcHelper.close(pstmt);
        return effectedRow > 0;
    }

    //查找一个user
    public Collection<User> findUsersByFacultyId(Integer id,Connection connection) throws SQLException {
        Collection<User> users = new TreeSet<>();
        String select_sql = "SELECT * FROM User WHERE faculty_id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(select_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //创建结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        while (resultSet.next()) {
            //用结果集中的字段为user对象的各字段赋值
            int idOfUser = resultSet.getInt("id");
            String username = resultSet.getString("username");
            String password = resultSet.getString("password");
            String name = resultSet.getString("name");
            String sex = resultSet.getString("sex");
            String politicsStatus = resultSet.getString("politicsStatus");
            String telephone = resultSet.getString("telephone");
            Faculty faculty = FacultyService.getInstance().find(
                    resultSet.getInt("faculty_id"));
            Boolean available = resultSet.getBoolean("available");
            String introduction = resultSet.getString("introduction");
            Collection<Role> roles = UserAssRoleService.getInstance().findRolesByUserId(id);
            User user = new User(idOfUser,username,password,name,sex,politicsStatus,
                    telephone,faculty,available,introduction,roles);
            users.add(user);
        }
        JdbcHelper.close(resultSet,pstmt);
        return users;
    }

    // 用户自己更新密码
    public boolean updatePasswordBySelf(User user, Connection connection) throws SQLException {
        String update_sql = "UPDATE User SET password=? WHERE id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(update_sql);
        //为预编译参数赋值
        pstmt.setString(1, user.getPassword());
        pstmt.setInt(2, user.getId());
        //判断是否修改成功
        int effectedRow = pstmt.executeUpdate();
        JdbcHelper.close(pstmt);
        return effectedRow > 0;
    }

    // 批量删除user
    public Integer deleteBatch(Collection<User> users,Connection connection) throws SQLException {
        int totalDeleteNum = 0;
        PreparedStatement pstmt = null;
        for (User user:users) {
            String delete_sql = "DELETE FROM User WHERE id = ?";
            //在连接上创建预编译语句对象
            pstmt = connection.prepareStatement(delete_sql);
            //为预编译参数赋值
            pstmt.setInt(1, user.getId());
            //判断是否删除成功
            int effectedRow = pstmt.executeUpdate();
            totalDeleteNum += effectedRow;
        }
        JdbcHelper.close(pstmt);
        return totalDeleteNum;
    }


    public User login(Connection connection, String username, String password) throws SQLException, BysjException {
        User currentUser = null;
        String sql_login = "SELECT * FROM user WHERE username =? and password=?";
        PreparedStatement pstmt_login = connection.prepareStatement(sql_login);
        pstmt_login.setString(1, username);
        pstmt_login.setString(2, password);
        ResultSet resultSet_login = pstmt_login.executeQuery();
        while(resultSet_login.next()) {
            //用结果集中的字段为user对象的各字段赋值
            int id = resultSet_login.getInt("id");
            String name = resultSet_login.getString("name");
            String sex = resultSet_login.getString("sex");
            String politicsStatus = resultSet_login.getString("politicsStatus");
            String telephone = resultSet_login.getString("telephone");
            Faculty faculty = FacultyService.getInstance().find(resultSet_login.getInt("faculty_id"));
            Boolean available = resultSet_login.getBoolean("available");
            String introduction = resultSet_login.getString("introduction");
            Collection<Role> roles = UserAssRoleService.getInstance().findRolesByUserId(id);
            currentUser = new User(id,username,password,name,sex,politicsStatus,
                    telephone,faculty,available,introduction,roles);
        }
        return currentUser;
    }

    public Collection<User> findNamesOfTeachers(Connection connection) throws SQLException {
        Collection<User> users = new TreeSet<>();
        String select_sql = "SELECT * FROM User";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(select_sql);
        //创建结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        while (resultSet.next()) {
            String username = resultSet.getString("username");
            char end = username.charAt(username.length()-1);
            //用结果集中的字段为user对象的各字段赋值
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            if (end == '1'){
                User user = new User(id,name);
                users.add(user);
            }
        }
        JdbcHelper.close(resultSet,pstmt);
        return users;
    }

    public Collection<User> findNamesOfLeaders(Connection connection) throws SQLException {
        Collection<User> users = new TreeSet<>();
        String select_sql = "SELECT * FROM User";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(select_sql);
        //创建结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        while (resultSet.next()) {
            String username = resultSet.getString("username");
            char end = username.charAt(username.length()-1);
            //用结果集中的字段为user对象的各字段赋值
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            if (end == '3'){
                User user = new User(id,name);
                users.add(user);
            }
        }
        JdbcHelper.close(resultSet,pstmt);
        return users;
    }
}
